Create database lps10
go
use lps10
go
create table quantri(
	maquantri int primary key not null,
	qtmatkhau varchar(20),
	quyen int
);
go

create table hinhthucvay
(
	mahtv int primary key not null,
	htvten varchar(30)not null,
maquantri int constraint FK1_hinhthucvay foreign key references quantri(maquantri)
);
go 
Create table tylelai
(
	matll int primary key not null,
	tllten varchar(30) not null,
	tllmuclai varchar(30),
maquantri int constraint FK1_tylelai foreign key references quantri(maquantri)
);
go 
create table mucphat
(
	mamp int primary key not null,
	mpsotienlai varchar(30),
	mptyle varchar(30),
maquantri int constraint FK1_mucphat foreign key references quantri(maquantri)
	
);
go
Create table nhanvien
(
	manv int primary key not null ,
    nvten varchar(30) not null,
	nvdiachi varchar(30),
	nvsdt int ,
	nvemail varchar(30),
	nvgioitinh char(1)not null,
	maquantri int constraint FK1_nhanvien foreign key references quantri(maquantri)
);
go
Create table khachhang 
(
	makh int primary key not null ,
	khten varchar(30) ,
	khdiachi varchar(30) ,
	khngaysinh datetime,
	khsdt varchar(15),
	khgioitinh char(1),
	khemail varchar(20),
	khcmt varchar(20),
	khngaytaohoso datetime,
manv int constraint FK1_khachhhang foreign key references nhanvien(manv)
	
);
go


Create table hopdongvayvon
(
	mahd int primary key not null ,
	makh int constraint FK1_hopdongvayvon foreign key references khachhang(makh) not null,
	hdvvtongtaisanthechap int,
	hdvvsotienvay int,
	mahtv int constraint FK2_hopdongvayvon foreign key references hinhthucvay(mahtv) not null,
	matll int constraint FK3_hopdongvayvon foreign key references tylelai(matll) not null,
	hdvvngayvay datetime,
	hdvvngayketthuc datetime	
);
go
create table tralai
(
	matralai int primary key not null,
	ngaydonglai datetime,
	mahd int constraint FK1_tralai foreign key references hopdongvayvon(mahd),
	makh int constraint FK2_tralai foreign key references khachhang(makh) not null,
	sotienvay int,
	mahtv int constraint FK4_tralai foreign key references hinhthucvay(mahtv) not null,
	matll int constraint FK5_tralai foreign key references tylelai(matll) not null,
	tienlai int
);
create table tinhtienphat
(
	matinhtienphat int primary key not null ,
	mahd int constraint FK1_tinhtienphat foreign key references hopdongvayvon(mahd),
	makh int constraint FK2_tinhtienphat foreign key references khachhang(makh) not null,
	sotienvay int,
	mahtv int constraint FK3_tinhtienphat foreign key references hinhthucvay(mahtv) not null,
	matll int constraint FK4_tinhtienphat foreign key references tylelai(matll) not null,
	mamp int constraint FK5_tinhtienphat foreign key references mucphat(mamp) not null,
	tienlai int,
	ngayphaidonglai datetime,
	tienphat int
);	
go
insert into quantri values('121','admin','1');
insert into quantri values('122','user','2');

select * from quantri
go
insert into hinhthucvay values('1','Vay mua nha','121');
insert into hinhthucvay values('2','Vay mua oto','121');
insert into hinhthucvay values('3','Vay du hoc','121');
insert into hinhthucvay values('4','Vay ca nhan','121');
	select * from hinhthucvay
go
insert into tylelai values('1','Vay mua nha','10%','121');
insert into tylelai values('2','Vay mua oto','8%','121');
insert into tylelai values('3','Vay du hoc','5%','121');
insert into tylelai values('4','Vay ca nhan','3%','121');
	select * from tylelai
go
insert into mucphat values('1','1.000.000--15.000.000','2%','121');
insert into mucphat values('2','15.100.000 --50.000.000','5%','121');
insert into mucphat values('3','50.100.000 --100.000.000','8%','121');
insert into mucphat values('4','100.100.000 --1.000.000.000','2%','121');
 	select * from mucphat
go